package cn.net.yto.school.dao.repositories;

import cn.net.yto.base.dao.BaseRepository;
import cn.net.yto.school.dao.dto.CourseDownloadByCourseStsDto;
import cn.net.yto.school.dao.dto.CourseDownloadStsDto;
import cn.net.yto.school.dao.entities.TbCourse;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * tb_course Repository
 * Created by gaojing
 * Wed Jul 18 20:11:47 CST 2018
 */
@Repository
public interface TbCourseRepository extends BaseRepository<TbCourse, Integer> {

    @Query(value = "SELECT tc.c_id, tc.c_name, tf.file_name, tf.file_download_count FROM tb_course tc, tb_file tf\n" +
            "\tWHERE tc.c_id = tf.file_belong_to", nativeQuery = true)
    List<CourseDownloadStsDto> courseDownloadSts();

    @Query(value = "SELECT t.cls_id, t.cls_name, t.c_id, t.c_name, tf.file_id, tf.file_name, tf.file_path, tf.file_discrib, tf.file_download_count FROM(\n" +
            "\tSELECT t.cls_id, t.cls_name, tsc.c_id, tc.c_name FROM\n" +
            "\t\t(SELECT tc.cls_id, tc.cls_name, ts.stu_id FROM tb_clazz tc, tb_student ts\n" +
            "\t\t\tWHERE tc.cls_id = ts.cls_id) t, tb_course_student tsc, tb_course tc\n" +
            "\t\tWHERE t.stu_id = tsc.stu_id AND tc.c_id = tsc.c_id\n" +
            ") t, tb_file tf \n" +
            "WHERE t.c_id = tf.file_belong_to; ", nativeQuery = true)
    List<CourseDownloadByCourseStsDto> courseDownloadByCourseSts();
}

